﻿Public Class clsFlbInfo
    Public Function save(flbInfo As entity_flb_info) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "	INSERT INTO sf.b_flb_info(factory_id , batch_no, dl_no , pgd_id , support_model , material_code, flb_type	 "
            szSql &= "	 , which_side , piece_num , used_piece_num , status , operator , used_date , update_time,assembling_date, exprie_date,b_gt_sys_id) 	 "
            szSql &= "	VALUES (  ? ,? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?, (SELECT CURRENT_TIMESTAMP(6)), (SELECT CURRENT_TIMESTAMP(6)),(SELECT CURRENT_TIMESTAMP(6) + '15 day'), ?)"
            With flbInfo
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", .batch_no, .dl_no, .pgd_id, .support_model, .material_code, .flb_type, .which_side,
                                             .piece_num, 0, .status, .oper, .used_date, .b_gt_sys_id)
            End With
            ' 查询最新添加的ID
            szSql = String.Empty
            szSql &= "SELECT * FROM sf.b_flb_info order by update_time desc limit 1"
            Dim dttable As DataTable = DBacs.ExecuteSql(szSql, -1)
            '执行入库操作
            szSql = String.Empty
            szSql &= "INSERT INTO sf.b_flb_ck(factory_id, dl_no, piece_num, which_side, flb_type, flb_info_sys_id, pgd_id, material_code, exprie_date, location, begin_date, status, into_ope, update_time) "
            szSql &= "VALUES (?, ?, ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6) + '15 day'), ?,(SELECT CURRENT_TIMESTAMP(6)),?,?,(SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", flbInfo.dl_no, flbInfo.piece_num, flbInfo.which_side, flbInfo.flb_type, dttable.Rows(0)("sys_id"), flbInfo.pgd_id, flbInfo.material_code, String.Empty, "0", flbInfo.oper)

            '更新法兰板吊篮主档忙闲字段
            szSql = String.Empty
            szSql &= "update sf.t_mst_dl set status = ? where dl_no = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", flbInfo.dl_no)
            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function save(flbInfo As entity_flb_info, dt As DataTable) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "	INSERT INTO sf.b_flb_info(factory_id , batch_no, dl_no , pgd_id , support_model , material_code, flb_type	 "
            szSql &= "	 , which_side , piece_num , used_piece_num , status , operator , used_date , update_time,assembling_date, exprie_date,b_gt_sys_id) 	 "
            szSql &= "	VALUES (  ? ,? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?, (SELECT CURRENT_TIMESTAMP(6)), (SELECT CURRENT_TIMESTAMP(6)),(SELECT CURRENT_TIMESTAMP(6) + '15 day'), ?)"
            With flbInfo
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", .batch_no, .dl_no, .pgd_id, .support_model, .material_code, .flb_type, .which_side,
                                             .piece_num, 0, .status, .oper, .used_date, .b_gt_sys_id)
            End With
            ' 查询最新添加的ID
            szSql = String.Empty
            szSql &= "SELECT * FROM sf.b_flb_info order by update_time desc limit 1"
            Dim dttable As DataTable = DBacs.ExecuteSql(szSql, -1)
            For index = 0 To dt.Rows.Count - 1
                szSql = "INSERT INTO sf.b_flb_info_details(b_flb_info_sys_id , piece_num, b_gt_sys_id)VALUES(?,?,?)"
                DBacs.ExecuteUpdateSql(szSql, -1, dttable.Rows(0)("sys_id"), dt.Rows(index)("into_box_num"), dt.Rows(index)("b_gt_sys_id"))
            Next
            '执行入库操作
            szSql = String.Empty
            szSql &= "INSERT INTO sf.b_flb_ck(factory_id, dl_no, piece_num, which_side, flb_type, flb_info_sys_id, pgd_id, material_code, exprie_date, location, begin_date, status, into_ope, update_time) "
            szSql &= "VALUES (?, ?, ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6) + '15 day'), ?,(SELECT CURRENT_TIMESTAMP(6)),?,?,(SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", flbInfo.dl_no, flbInfo.piece_num, flbInfo.which_side, flbInfo.flb_type, dttable.Rows(0)("sys_id"), flbInfo.pgd_id, flbInfo.material_code, String.Empty, "0", flbInfo.oper)
            '更新法兰板吊篮主档忙闲字段
            szSql = String.Empty
            szSql &= "update sf.t_mst_dl set status = ? where dl_no = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", flbInfo.dl_no)
            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function save(num As String, to_sys_id As String, from_sys_id As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql = "update sf.b_flb_info set piece_num = piece_num + ? where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, num, to_sys_id)
            szSql = "update sf.b_flb_info set piece_num = piece_num - ? where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, num, from_sys_id)
            szSql &= "SELECT * FROM sf.b_flb_info where sys_id = ? "
            Dim dttable As DataTable = DBacs.ExecuteSql(szSql, -1, from_sys_id)
            If dttable.Rows(0)("piece_num").ToString().Equals("0") Then
                '更新法兰板吊篮主档忙闲字段
                szSql = String.Empty
                szSql &= "update sf.t_mst_dl set "
                szSql &= " status = ? "
                szSql &= " where dl_no = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", dttable.Rows(0)("dl_no").ToString())
            End If
            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function save(which_side As String, piece_num As String, used_piece_num As String, sys_id As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        szSql = "update sf.b_flb_info set which_side = ?, piece_num = ?, used_piece_num = ? where sys_id = ? "
        rtn = DBacs.ExecuteUpdateSql(szSql, -1, which_side, piece_num, used_piece_num, sys_id)
        Return rtn
    End Function

    Public Function saveUpdate(piece_num As String, used_piece_num As String, sys_id As String) As Integer
        Dim szSql As String
        Dim rtn As Integer = -2
        Dim piece_num_integer As Integer = Integer.Parse(piece_num)
        Dim used_piece_num_integer As Integer = Integer.Parse(used_piece_num)
        If used_piece_num_integer = 0 Then
            szSql = "update sf.b_flb_info set used_piece_num = ?,status = ? where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, used_piece_num, "0", sys_id)
        End If
        If piece_num_integer > used_piece_num_integer Then
            szSql = "update sf.b_flb_info set used_piece_num = ?,status = ? where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, used_piece_num, "1", sys_id)
        End If
        If piece_num_integer = used_piece_num_integer Then
            szSql = "update sf.b_flb_info set used_piece_num = ?,status = ? where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, used_piece_num, "2", sys_id)
        End If
        Return rtn
    End Function

    Public Function saveDelete(dl_no As String, sys_id As String) As Integer
        Dim szSql As String
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            '执行删除
            szSql = " delete from sf.b_flb_info where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, sys_id)

            '更新法兰板吊篮主档忙闲字段
            szSql = "update sf.t_mst_dl set status = ? where dl_no = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", dl_no)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function getFlbInfoListByGtSysId(gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT COALESCE(sum(piece_num), '0') as piece_num_all FROM sf.b_flb_info WHERE b_gt_sys_id = ?"
        Return DBacs.ExecuteSql(szSql, -1, gt_sys_id)
    End Function

    Public Function getFlbInfoDetailsListByGtSysId(gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT COALESCE(sum(piece_num), '0') as piece_num_all FROM sf.b_flb_info_details WHERE b_gt_sys_id = ?"
        Return DBacs.ExecuteSql(szSql, -1, gt_sys_id)
    End Function

    Public Function getListByGtSysId(gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_flb_info WHERE b_gt_sys_id = ?"
        Return DBacs.ExecuteSql(szSql, -1, gt_sys_id)
    End Function

    Public Function getFlbInfoListByStatus(status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_flb_info WHERE status = ? and piece_num > 0 "
        Return DBacs.ExecuteSql(szSql, -1, status)
    End Function

    Public Function getFlbInfoListByUsedStatus(status As String, flb_no As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_flb_info WHERE status <> ? and piece_num > 0 "
        If String.IsNullOrEmpty(flb_no) = False Then
            szSql &= " and dl_no = '" & flb_no & "'"
        End If
        Return DBacs.ExecuteSql(szSql, -1, status)
    End Function

    Public Function getListByDlNo(dl_no As String) As DataTable
        Dim szSql As String = String.Empty
        szSql &= "SELECT a.*,b.material_code as flb_material_code from sf.b_flb_info a left join sf.b_gt b on a.b_gt_sys_id = b.sys_id where a.factory_id = ? And a.dl_no = ? order by assembling_date desc"
        Return DBacs.ExecuteSql(szSql, -1, "TS", dl_no)
    End Function

    Public Function getListByDlNoAndStatus(dl_no As String, status As String) As DataTable
        Dim szSql As String = String.Empty
        szSql &= "SELECT a.*,b.material_code as flb_material_code from sf.b_flb_info a left join sf.b_gt b on a.b_gt_sys_id = b.sys_id where a.factory_id = ? And a.dl_no = ? and a.status <> ? order by assembling_date desc"
        Return DBacs.ExecuteSql(szSql, -1, "TS", dl_no, status)
    End Function

    Public Function getFlbInfoByDlNo(dl_no As String) As DataTable
        Dim szSql As String = "SELECT a.*,b.material_code as flb_material_code FROM sf.b_flb_info a left join sf.b_gt b on a.b_gt_sys_id = b.sys_id WHERE a.dl_no = ? and a.piece_num > 0 AND a.status <> '2'"
        Return DBacs.ExecuteSql(szSql, -1, dl_no)
    End Function

End Class
